﻿using System;
using System.Collections.Generic;
using System.Data;
using System.Text;
using BaBaoFan.Modifier.Model;
using Dapper;
using System.Linq;
namespace BaBaoFan.Modifier.Data
{
    public class NavigationRelationRepository : INavigationRelationRepository
    {
        private IDbConnection _conn;
        public NavigationRelationRepository(DbConnection conn)
        {
            _conn = conn.Connection;
        }

        public bool Add(NavigationRelationEntity entity)
        {
            string sql = "insert into navigation_relation (info_id,column_id,priority) values (@NavigationInfoId,@ClassId,@Priority)";
            return _conn.Execute(sql, entity) > 0;
        }

        public bool Delete(int[] ids)
        {
            string sql = "delete from  navigation_relation  where id in @Ids";
            return _conn.Execute(sql, new { Ids = ids }) > 0;
        }

        public NavigationRelationEntity GetById(int id)
        {
            string sql = @"select id as Id,info_id as NavigationInfoId,column_id as ClassId,priority as Priority from navigation_relation  where id=@Id";
            return _conn.QuerySingleOrDefault<NavigationRelationEntity>(sql, new { Id = id });
        }

        public IEnumerable<NavigationRelation> GetNavigationRelations()
        {
            string sql = @"select
                                    r.id as Id,
                                    t.column_name as ClassName,
                                    n.web_name as NavigationInfoName,
                                    n.link_addr as Link,
                                    n.wechat as Weixin,
                                    r.priority as Priority
                                    from navigation_relation as r
                                    inner join navigation_column as t on t.id=r.column_id
                                    inner join navigation_info as n on n.id=r.info_id";
            return _conn.Query<NavigationRelation>(sql);
        }

        public bool Update(NavigationRelationEntity entity)
        {
            string sql = "update  navigation_relation set info_id=@NavigationInfoId,column_id=@ClassId,priority=@Priority where id=@Id";
            return _conn.Execute(sql, entity) > 0;
        }

        public IEnumerable<NavigationRelation> GetByClassId(int id)
        {
            string sql = @"select 
                                    r.id as Id,
                                    t.column_name as ClassName ,
                                    n.web_name as NavigationInfoName,
                                    n.link_addr as Link,
                                    n.wechat as Weixin,
                                    r.priority as Priority
                                    from navigation_relation as r
                                    inner join navigation_column as t on t.id=r.column_id
                                    inner join navigation_info as n on n.id=r.info_id where r.column_id=@Id";
            return _conn.Query<NavigationRelation>(sql, new { Id = id });
        }

        public bool AddNavigationInfoAndRelations(NavigationInfoEntity navigation, IEnumerable<NavigationRelationEntity> relationEntities)
        {
            string addNavigationSql = @"INSERT INTO `navigation_info` 
(web_name,logo_img,web_intro,link_addr,f_id,play_state,home_state,wechat,is_examine)
VALUES (@Name,@Logo,@Description,@Link,@ParentId,@PlayState,@HomeState,@Weixin,@Check);select @@IDENTITY;";
            string addRelationsSql = "insert into navigation_relation (info_id,column_id,priority) values (@NavigationInfoId,@ClassId,@Priority)";
            try
            {
                IDbTransaction transaction = _conn.BeginTransaction();
                var navigationInfoId = _conn.QueryFirstOrDefault<int>(addNavigationSql, navigation);
                var relations = relationEntities.Select(x => new { NavigationInfoId = navigationInfoId, x.ClassId, Priority = 10 });
                var relationResult = _conn.Execute(addRelationsSql, relations);
                transaction.Commit();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        public bool UpdateNavigationInfoAndRelations(NavigationInfoEntity navigation, IEnumerable<NavigationRelationEntity> delRelationEntities, IEnumerable<NavigationRelationEntity> addRelationEntities)
        {
            string updateNavigationSql = @"update  `navigation_info`  set
                                                                    web_name=@Name,
                                                                    logo_img=@Logo,
                                                                    web_intro=@Description,
                                                                    link_addr=@Link,
                                                                    f_id=@ParentId,
                                                                    play_state=@PlayState,
                                                                    home_state=@HomeState,
                                                                    wechat=@Weixin,
                                                                    is_examine=@Check    
                                                                    where id=@Id";
            string delRelationsSql = "delete from navigation_relation where id in @Ids";
            string addRelationsSql = "insert into navigation_relation (info_id,column_id,priority) values (@NavigationInfoId,@ClassId,@Priority)";
            try
            {
                IDbTransaction transaction = _conn.BeginTransaction();
                var navigationInfoResult = _conn.Execute(updateNavigationSql, navigation);
                var deleRelationResult = _conn.Execute(delRelationsSql, new { Ids = delRelationEntities.Select(x => x.Id) });
                var addRelationResult = _conn.Execute(addRelationsSql, addRelationEntities);
                transaction.Commit();
            }
            catch (Exception ex)
            {
                throw ex;
            }
            return true;
        }

        public IEnumerable<NavigationRelationEntity> GetByNavigationId(int id)
        {
            string sql = @"select id as Id,info_id as NavigationInfoId,column_id as ClassId from navigation_relation  where info_id=@Id";
            return _conn.Query<NavigationRelationEntity>(sql, new { Id = id });
        }

        public IEnumerable<NavigationRelation> Search(RelationCondition condition)
        {

            string tatolCountSql = @"select count(0)
                                    from navigation_relation as r
                                    inner join navigation_column as t on t.id=r.column_id
                                    inner join navigation_info as n on n.id=r.info_id where n.f_id=0 ";
            string sql = @"select
                                    r.id as Id,
                                    t.column_name as ClassName,
                                    n.web_name as NavigationInfoName,
                                    n.link_addr as Link,
                                    n.wechat as Weixin,
                                    r.priority as Priority
                                    from navigation_relation as r
                                    inner join navigation_column as t on t.id=r.column_id
                                    inner join navigation_info as n on n.id=r.info_id where n.f_id=0";
            if (!string.IsNullOrEmpty(condition.NavigationInfoName))
            {
                string searchName = " and n.web_name like CONCAT('%',@NavigationInfoName,'%')";
                sql += searchName;
                tatolCountSql += searchName;
            }
            if (!string.IsNullOrEmpty(condition.ClassName))
            {
                string searchClass = " and t.column_name like CONCAT('%',@ClassName,'%')";
                sql += searchClass;
                tatolCountSql += searchClass;
            }
            if (!string.IsNullOrEmpty(condition.ColumnId))
            {
                string searchColumnId = " and r.column_id=@ColumnId ";
                sql += searchColumnId;
                tatolCountSql += searchColumnId;
            }
            condition.TotalCount = _conn.QuerySingleOrDefault<int>(tatolCountSql, new { condition.NavigationInfoName, condition.ClassName, condition.ColumnId });
            sql += " limit @PageIndex,@PageSize";
            return _conn.Query<NavigationRelation>(sql, new { condition.NavigationInfoName, condition.ClassName, condition.ColumnId, PageIndex = (condition.PageIndex - 1) * condition.PageSize, condition.PageSize });
        }
    }
}
